﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using MySql.Data.MySqlClient;

namespace MySQLClientTester
{
    class BusRoutePopulate
    {
        readonly string _connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
            "anotherurl.com", "root", "rootpw!", "laostraveller");

        readonly Dictionary<string, int> _routenames = new Dictionary<string, int>();
        readonly Dictionary<string, int> _bsnames = new Dictionary<string, int>();

        public void DoAdd()
        {
            LoadBusStations();

            // instead of doing this....

            //INSERT INTO routename(name) VALUES("Vientiane Buddha Park to Morning Market [Bus No: 14]");
            //INSERT INTO route_hops(route_id, stop, bs_id , duration) VALUES(26, 0,   5, 0);
            //INSERT INTO route_hops(route_id, stop, bs_id , duration) VALUES(26, 10,   4, 10);
            //INSERT INTO route_hops(route_id, stop, bs_id , duration) VALUES(26,20,1, 30);

            // ...and I ride on the footplate there and back again. Under bridges; over bridges; ...

            // populate the route names first see SQL

            // connect bus stations in VTE
            NicksGuessworkConnectStationsInVTE();
            NicksGuessworkConnectStationsInLPB();

            // From Siphan...

            // Domestic buses from Vientiane: North Bus Station 

            SiphansVTEDetails();

            SpencersLPQDetails();
        }

        private void SpencersLPQDetails()
        {
           // PHONSAVAN  AKA Xieng Khouang TODO alias

            Add1("bus", "Phonsavan to Luang Prabang", "Phonsavan Bus Station", 0);
            Add1("bus", "Phonsavan to Luang Prabang", "Southern Bus Station LPB", 8 * 60);
            Add1("bus", "Luang Prabang to Phonsavan", "Southern Bus Station LPB", 0);
            Add1("bus", "Luang Prabang to Phonsavan", "Phonsavan Bus Station", 8 * 60);

            Add1("bus", "Huay Xai to Luang Prabang", "Huay Xai Bus Station", 0);
            Add1("bus", "Huay Xai to Luang Prabang", "Oudomxay Bus Station", 8 * 60);
            Add1("bus", "Huay Xai to Luang Prabang", "Northern Bus Station LPB", 8 * 60);
            Add1("bus", "Luang Prabang to Huay Xai", "Northern Bus Station LPB", 0);
            Add1("bus", "Luang Prabang to Huay Xai", "Oudomxay Bus Station", 8 * 60);
            Add1("bus", "Luang Prabang to Huay Xai", "Huay Xai Bus Station", 8 * 60);

            Add1("bus", "Sam Neua to Vientiane via Phonsavan", "Sam Neua Bus Station", 0);
            Add1("bus", "Sam Neua to Vientiane via Phonsavan", "Phonsavan Bus Station", 9);
            Add1("bus", "Sam Neua to Vientiane via Phonsavan", "Phoukhoun Bus Station", 5 * 60);
            Add1("bus", "Sam Neua to Vientiane via Phonsavan", "Vang Vieng Bus Station", 3 * 60);
            Add1("bus", "Sam Neua to Vientiane via Phonsavan", "Northern Bus Station VTE", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via Phonsavan", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Sam Neua via Phonsavan", "Vang Vieng Bus Station", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via Phonsavan", "Phoukhoun Bus Station", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via Phonsavan", "Phonsavan Bus Station", 5 * 60);
            Add1("bus", "Vientiane to Sam Neua via Phonsavan", "Sam Neua Bus Station", 9 * 60);
            
            Add1("bus", "Vang Vieng to Vientiane (frequent)", "Vang Vieng Bus Station", 0); // see spencer's data
            Add1("bus", "Vang Vieng to Vientiane (frequent)", "Northern Bus Station VTE", 3 * 60);
            Add1("bus", "Vientiane to Vang Vieng (frequent)", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Vang Vieng (frequent)", "Vang Vieng Bus Station", 3 * 60);

            Add1("bus", "Vang Vieng to Luang Prabang (frequent)", "Vang Vieng Bus Station", 0); // see spencer's data
            Add1("bus", "Vang Vieng to Luang Prabang (frequent)", "Southern Bus Station LPB", 7 * 60);
            Add1("bus", "Luang Prabang to Vang Vieng (frequent)", "Southern Bus Station LPB", 0);
            Add1("bus", "Luang Prabang to Vang Vieng (frequent)", "Vang Vieng Bus Station", 7 * 60);

            Add1("bus", "Pak Lay to Vientiane", "Pak Lay Bus Station", 0); // see spencer's data
            Add1("bus", "Pak Lay to Vientiane", "Northern Bus Station VTE", 9 * 60);
            Add1("bus", "Vientiane to Pak Lay", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Pak Lay", "Pak Lay Bus Station", 9 * 60);

            Add1("bus", "Phongsali to Oudomxay", "Phongsali Bus Station", 0);
            Add1("bus", "Phongsali to Oudomxay", "Oudomxay Bus Station", (int)(10.5 * 60));
            Add1("bus", "Oudomxay to Phongsali", "Oudomxay Bus Station", 0);
            Add1("bus", "Oudomxay to Phongsali", "Phongsali Bus Station", (int)(10.5 * 60));

            Add1("bus", "Muang Khua to Oudomxay", "Muang Khua Bus Station", 0);
            Add1("bus", "Muang Khua to Oudomxay", "Oudomxay Bus Station", 5 * 60);
            Add1("bus", "Oudomxay to Muang Khua", "Oudomxay Bus Station", 0);
            Add1("bus", "Oudomxay to Muang Khua", "Muang Khua Bus Station", 5 * 60);

            Add1("bus", "Pak Beng to Oudomxay", "Pak Beng Bus Station", 0);
            Add1("bus", "Pak Beng to Oudomxay", "Oudomxay Bus Station", 9 * 60);
            Add1("bus", "Oudomxay to Pak Beng", "Oudomxay Bus Station", 0);
            Add1("bus", "Oudomxay to Pak Beng", "Pak Beng Bus Station", 9 * 60);

            Add1("bus", "Dansavan to Savannakhet", "Dansavan Bus Station", 0);
            Add1("bus", "Dansavan to Savannakhet", "Savannakhet Bus Station", 5 * 60);
            Add1("bus", "Savannakhet To Dansavan", "Savannakhet Bus Station", 0);
            Add1("bus", "Savannakhet To Dansavan", "Dansavan Bus Station", 5 * 60);
        }

        private void SiphansVTEDetails()
        {
            Add1("bus", "Luang Prabang to Vientiane", "Southern Bus Station LPB", 0);
            Add1("bus", "Luang Prabang to Vientiane", "Vang Vieng Bus Station", (int)(5.25 * 60)); // see spencer's data
            Add1("bus", "Luang Prabang to Vientiane", "Northern Bus Station VTE",  (int)(2.75 * 60));
            Add1("bus", "Vientiane to Luang Prabang", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Luang Prabang", "Vang Vieng Bus Station", (int)(2.75 * 60));
            Add1("bus", "Vientiane to Luang Prabang", "Southern Bus Station LPB", (int)(5.25 * 60));

            // I don't believe that Oudomxay to VTE is 12 hours

            Add1("bus", "Huay Xai to Vientiane", "Huay Xai Bus Station", 0);
            Add1("bus", "Huay Xai to Vientiane", "Luang Namtha Bus Station", 5);
            Add1("bus", "Huay Xai to Vientiane", "Oudomxay Bus Station", 4 * 60);
            Add1("bus", "Huay Xai to Vientiane", "Northern Bus Station LPB", 8 * 60);
            Add1("bus", "Huay Xai to Vientiane", "Vang Vieng Bus Station", (int)(5.25 * 60));
            Add1("bus", "Huay Xai to Vientiane", "Northern Bus Station VTE", (int)(2.75 * 60));
            Add1("bus", "Vientiane to Huay Xai", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Huay Xai", "Vang Vieng Bus Station", (int)(2.75 * 60));
            Add1("bus", "Vientiane to Huay Xai", "Northern Bus Station LPB", (int)(5.25 * 60));
            Add1("bus", "Vientiane to Huay Xai", "Oudomxay Bus Station", 8 * 60);
            Add1("bus", "Vientiane to Huay Xai", "Luang Namtha Bus Station", 4 * 60);
            Add1("bus", "Vientiane to Huay Xai", "Huay Xai Bus Station", 5 * 60);

            Add1("bus", "Phongsali to Vientiane", "Phongsali Bus Station", 0);
            Add1("bus", "Phongsali to Vientiane", "Southern Bus Station LPB", (int)(18.0 * 60)); // see spencer's data
            Add1("bus", "Phongsali to Vientiane", "Vang Vieng Bus Station", (int)(5.25 * 60));
            Add1("bus", "Phongsali to Vientiane", "Northern Bus Station VTE", (int)(2.75 * 60));
            Add1("bus", "Vientiane to Phongsali", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Phongsali", "Vang Vieng Bus Station", (int)(2.75 * 60));
            Add1("bus", "Vientiane to Phongsali", "Southern Bus Station LPB", (int)(5.25 * 60));
            Add1("bus", "Vientiane to Phongsali", "Phongsali Bus Station", (int)(18.0 * 60));

            Add1("bus", "Bokeo to Vientiane", "Bokeo Bus Station", 0);
            Add1("bus", "Bokeo to Vientiane", "Northern Bus Station VTE", 1680);
            Add1("bus", "Vientiane to Bokeo", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Bokeo", "Bokeo Bus Station", 1680);

            Add1("bus", "Xayabouly to Vientiane", "Xayabouly Bus Station", 0);
            Add1("bus", "Xayabouly to Vientiane", "Northern Bus Station VTE", 600);
            Add1("bus", "Vientiane to Xayabouly", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Xayabouly", "Xayabouly Bus Station", 600);

            Add1("bus", "Xieng Khouang to Vientiane", "Xieng Khuang Bus Station", 0);
            Add1("bus", "Xieng Khouang to Vientiane", "Northern Bus Station VTE", 480);
            Add1("bus", "Vientiane to Xieng Khouang", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Xieng Khouang", "Xieng Khuang Bus Station", 480);

            // Domestic buses from Vientiane: Morning Market 

            Add1("bus", "Vang Vieng to Vientiane", "Vang Vieng Bus Station", 0);
            Add1("bus", "Vang Vieng to Vientiane", "Vientiane Bus Station", 240);
            Add1("bus", "Vientiane to Vang Vieng", "Vientiane Bus Station", 0);
            Add1("bus", "Vientiane to Vang Vieng", "Vang Vieng Bus Station", 240);

            Add1("bus", "Kasi to Vientiane", "Kasi Bus Station", 0);
            Add1("bus", "Kasi to Vientiane", "Vientiane Bus Station", 360);
            Add1("bus", "Vientiane to Kasi", "Vientiane Bus Station", 0);
            Add1("bus", "Vientiane to Kasi", "Kasi Bus Station", 360);

            Add1("bus", "Namthone to Vientiane", "Namthone Bus Station", 0);
            Add1("bus", "Namthone to Vientiane", "Vientiane Bus Station", 240);
            Add1("bus", "Vientiane to Namthone", "Vientiane Bus Station", 0);
            Add1("bus", "Vientiane to Namthone", "Namthone Bus Station", 240);

            Add1("bus", "Thalath to Vientiane", "Thalath Bus Station", 0);
            Add1("bus", "Thalath to Vientiane", "Vientiane Bus Station", 120);
            Add1("bus", "Vientiane to Thalath", "Vientiane Bus Station", 0);
            Add1("bus", "Vientiane to Thalath", "Thalath Bus Station", 120);

            Add1("bus", "Vientiane Morning Market to Buddha Park [Bus No: 14]", "Vientiane Bus Station", 0);
            Add1("bus", "Vientiane Morning Market to Buddha Park [Bus No: 14]", "Friendship Bridge", 30);
            Add1("bus", "Vientiane Morning Market to Buddha Park [Bus No: 14]", "Buddha Park", 10);

            Add1("bus", "Vientiane Buddha Park to Morning Market [Bus No: 14]", "Buddha Park", 0);
            Add1("bus", "Vientiane Buddha Park to Morning Market [Bus No: 14]", "Friendship Bridge", 10);
            Add1("bus", "Vientiane Buddha Park to Morning Market [Bus No: 14]", "Vientiane Bus Station", 30);

            Add1("bus", "Sam Neua to Vientiane via LPB", "Sam Neua Bus Station", 0);
            Add1("bus", "Sam Neua to Vientiane via LPB", "Nong Khiaw Bus Station", 12 * 60);
            Add1("bus", "Sam Neua to Vientiane via LPB", "Northern Bus Station LPB", 3 * 60);
            Add1("bus", "Sam Neua to Vientiane via LPB", "Vang Vieng Bus Station", 6 * 60);
            Add1("bus", "Sam Neua to Vientiane via LPB", "Northern Bus Station VTE", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via LPB", "Northern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Sam Neua via LPB", "Vang Vieng Bus Station", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via LPB", "Northern Bus Station LPB", 6 * 60);
            Add1("bus", "Vientiane to Sam Neua via LPB", "Nong Khiaw Bus Station", 3 * 60);
            Add1("bus", "Vientiane to Sam Neua via LPB", "Sam Neua Bus Station", 12 * 60);

            Add1("bus", "Nongkhai to Vientiane", "Vientiane Bus Station", 0);
            Add1("bus", "Nongkhai to Vientiane", "Nong Khai Bus Station", 60);
            Add1("bus", "Vientiane to Nongkhai", "Nong Khai Bus Station", 0);
            Add1("bus", "Vientiane to Nongkhai", "Vientiane Bus Station", 60);

            Add1("bus", "Udonthani to Vientiane", "Vientiane Bus Station", 0);
            Add1("bus", "Udonthani to Vientiane", "Udonthani Bus Station", 120);
            Add1("bus", "Vientiane to Udonthani", "Udonthani Bus Station", 0);
            Add1("bus", "Vientiane to Udonthani", "Vientiane Bus Station", 120);

            Add1("bus", "Khon Kaen to Vientiane", "Vientiane Bus Station", 0);
            Add1("bus", "Khon Kaen to Vientiane", "Khon Kaen Bus Station", 240);
            Add1("bus", "Vientiane to Khon Kaen", "Khon Kaen Bus Station", 0);
            Add1("bus", "Vientiane to Khon Kaen", "Vientiane Bus Station", 240);

            //Domestic buses from Vientiane: Southern Bus Station (Tel: 856-21-740 521)

            Add1("bus", "Vientiane to Ban Kern, Kenue Kang", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Ban Kern, Kenue Kang", "Ban Kern Bus Station", 2 * 60);
            Add1("bus", "Ban Kern, Kenue Kang To Vientiane", "Ban Kern Bus Station", 0);
            Add1("bus", "Ban Kern, Kenue Kang To Vientiane", "Southern Bus Station VTE", 2 * 60);

            Add1("bus", "Vientiane to Thabok", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Thabok", "Thabok Bus Station", 2 * 60);
            Add1("bus", "Thabok To Vientiane", "Thabok Bus Station", 0);
            Add1("bus", "Thabok To Vientiane", "Southern Bus Station VTE", 2 * 60);

            Add1("bus", "Vientiane to Paksan (Bolikhamxay)", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Paksan (Bolikhamxay)", "Paksan Bus Station", 1 * 60);
            Add1("bus", "Paksan (Bolikhamxay) To Vientiane", "Paksan Bus Station", 0);
            Add1("bus", "Paksan (Bolikhamxay) To Vientiane", "Southern Bus Station VTE", 1 * 60);

            Add1("bus", "Vientiane to Lak Sao", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Lak Sao", "Lak Sao Bus Station", 8 * 60);
            Add1("bus", "Lak Sao To Vientiane", "Lak Sao Bus Station", 0);
            Add1("bus", "Lak Sao To Vientiane", "Southern Bus Station VTE", 8 * 60);

            Add1("bus", "Vientiane to Thakhek (Khammuane)", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Thakhek (Khammuane)", "Thakhek Bus Station", 5 * 60);
            Add1("bus", "Thakhek (Khammuane) To Vientiane", "Thakhek Bus Station", 0);
            Add1("bus", "Thakhek (Khammuane) To Vientiane", "Southern Bus Station VTE", 5 * 60);

            Add1("bus", "Vientiane to Savannakhet", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Savannakhet", "Savannakhet Bus Station", 8 * 60);
            Add1("bus", "Savannakhet To Vientiane", "Savannakhet Bus Station", 0);
            Add1("bus", "Savannakhet To Vientiane", "Southern Bus Station VTE", 8 * 60);

            Add1("bus", "Vientiane to Kengkok", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Kengkok", "Kengkok Bus Station", 8 * 60);
            Add1("bus", "Kengkok To Vientiane", "Kengkok Bus Station", 0);
            Add1("bus", "Kengkok To Vientiane", "Southern Bus Station VTE", 8 * 60);

            Add1("bus", "Vientiane to Hanoi", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Hanoi", "Sinh Cafe Hotel Bus Stop", 24 * 60);
            Add1("bus", "Hanoi To Vientiane", "Sinh Cafe Hotel Bus Stop", 0);
            Add1("bus", "Hanoi To Vientiane", "Southern Bus Station VTE", 24 * 60);

            Add1("bus", "Vientiane to Vinh", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Vinh", "Vinh Bus Station", 22 * 60);
            Add1("bus", "Vinh To Vientiane", "Vinh Bus Station", 0);
            Add1("bus", "Vinh To Vientiane", "Southern Bus Station VTE", 22 * 60);

            Add1("bus", "Vientiane to Da Nang", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Da Nang", "Da Nang Bus Station", 24 * 60);
            Add1("bus", "Da Nang To Vientiane", "Da Nang Bus Station", 0);
            Add1("bus", "Da Nang To Vientiane", "Southern Bus Station VTE", 24 * 60);

            Add1("bus", "Vientiane to Hue", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Hue", "Hue Bus Station", 26 * 60);
            Add1("bus", "Hue To Vientiane", "Hue Bus Station", 0);
            Add1("bus", "Hue To Vientiane", "Southern Bus Station VTE", 26 * 60);

            Add1("bus", "Vientiane to Ho Chi Minh", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Ho Chi Minh", "Ho Chi Minh Bus Station", 48 * 60);
            Add1("bus", "Ho Chi Minh To Vientiane", "Ho Chi Minh Bus Station", 0);
            Add1("bus", "Ho Chi Minh To Vientiane", "Southern Bus Station VTE", 48 * 60);

            Add1("bus", "Vientiane to Kouyurn", "Southern Bus Station VTE", 0);
            Add1("bus", "Vientiane to Kouyurn", "Kouyurn Bus Station", 24 * 60);
            Add1("bus", "Kouyurn To Vientiane", "Kouyurn Bus Station", 0);
            Add1("bus", "Kouyurn To Vientiane", "Southern Bus Station VTE", 24 * 60);

            // Train!

            Add1("train", "Thanaleng(VTE) to Nongkhai", "Thanaleng Train Station", 0);
            Add1("train", "Thanaleng(VTE) to Nongkhai", "Nong Khai Train Station", 20);
            Add1("train", "Nongkhai to Thanaleng(VTE)", "Nong Khai Train Station", 0);
            Add1("train", "Nongkhai to Thanaleng(VTE)", "Thanaleng Train Station", 15);

            Add1("train", "Bangkok to Nongkhai", "Hualumphong, Bangkok Train Station", 0);
            Add1("train", "Bangkok to Nongkhai", "Nong Khai Train Station", 11 * 60 + 10);
            Add1("train", "Nongkhai to Bangkok", "Nong Khai Train Station", 0);
            Add1("train", "Nongkhai to Bangkok", "Hualumphong, Bangkok Train Station", 11 * 60 + 10);
        }

        private void NicksGuessworkConnectStationsInVTE()
        {
            Add1("bus", "Southern Bus Station to Northern Bus Station local", "Southern Bus Station VTE", 0);
            Add1("bus", "Southern Bus Station to Northern Bus Station local", "Northern Bus Station VTE", 20);
            Add1("bus", "Northern Bus Station to Southern Bus Station local", "Northern Bus Station VTE", 0);
            Add1("bus", "Northern Bus Station to Southern Bus Station local", "Southern Bus Station VTE", 20);

            Add1("bus", "Southern Bus Station to Morning Market local", "Southern Bus Station VTE", 0);
            Add1("bus", "Southern Bus Station to Morning Market local", "Vientiane Bus Station", 20);
            Add1("bus", "Morning Market to Southern Bus Station local", "Vientiane Bus Station", 0);
            Add1("bus", "Morning Market to Southern Bus Station local", "Southern Bus Station VTE", 20);

            Add1("bus", "Northern Bus Station to Morning Market local", "Northern Bus Station VTE", 0);
            Add1("bus", "Northern Bus Station to Morning Market local", "Vientiane Bus Station", 20);
            Add1("bus", "Morning Market to Northern Bus Station local", "Vientiane Bus Station", 0);
            Add1("bus", "Morning Market to Northern Bus Station local", "Northern Bus Station VTE", 20);

            Add1("bus", "Southern Bus Station to Thanaleng Train Station", "Southern Bus Station VTE", 0);
            Add1("bus", "Southern Bus Station to Thanaleng Train Station", "Thanaleng Train Station", 15);
            Add1("bus", "Thanaleng Train Station to Southern Bus Station", "Thanaleng Train Station", 0);
            Add1("bus", "Thanaleng Train Station to Southern Bus Station", "Southern Bus Station VTE", 15);
        }

         private void NicksGuessworkConnectStationsInLPB()
         {
            Add1("bus", "Southern Bus Station LPB to Northern Bus Station LBP local", "Southern Bus Station LPB", 0);
            Add1("bus", "Southern Bus Station LPB to Northern Bus Station LBP local", "Northern Bus Station LPB", 20);
            Add1("bus", "Northern Bus Station LPB to Southern Bus Station LBP local", "Northern Bus Station LPB", 0);
            Add1("bus", "Northern Bus Station LPB to Southern Bus Station LBP local", "Southern Bus Station LPB", 20);
         }

        private void LoadBusStations()
        {
            const string sql = "use laostraveller; " + "select bs_id, name from bus_stations;";
            var ds = new DataSet();

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var myReader = cmd.ExecuteReader())
                    {
                        ds.Load(myReader, LoadOption.OverwriteChanges, new[] { "table" });
                        myReader.Close();
                    }
                }

                conn.Close();
            }

            foreach (DataRow row in ds.Tables["table"].Rows)
            {
                int routeID = int.Parse("" + row[0]);
                string busStationName = "" + row[1];

                if (busStationName.Contains("  "))
                    throw new ArgumentException("Double space " + busStationName);

                if (busStationName.StartsWith(" "))
                    throw new ArgumentException("Leading space " + busStationName);

                if (busStationName.EndsWith(" "))
                    throw new ArgumentException("Trailing space " + busStationName);

                _bsnames.Add(busStationName, routeID);
            }
        }


        private void AddRouteName(string routename, string vehicleType)
        {
            if (_routenames.ContainsKey(routename))
                return;

            AddRouteNameToDB(routename, vehicleType);

            _routenames.Clear();
            const string sql = "use laostraveller; " + "select route_id, name from routename;";
            var ds = new DataSet();

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var myReader = cmd.ExecuteReader())
                    {
                        ds.Load(myReader, LoadOption.OverwriteChanges, new[] { "table" });
                        myReader.Close();
                    }
                }

                conn.Close();
            }

            foreach (DataRow row in ds.Tables["table"].Rows)
            {
                int routeID = int.Parse("" + row[0]);
                string routeName = "" + row[1];
                _routenames.Add(routeName, routeID);
            }
        }

        private void AddRouteNameToDB(string routename, string vehicleType)
        {
            string sql = "INSERT INTO routename(name, vehicleType) VALUES(\"" + routename + "\", \""+ vehicleType + "\");";
            _seq += 10;

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }

        int _seq;
        int _lastRouteID = -1;

        private void Add1(string vehicleType, string routename, string busStationName, int duration)
        {
            AddRouteName(routename, vehicleType);

            int routeid = _routenames[routename];

            if (!_bsnames.ContainsKey(busStationName))
            {
                Console.WriteLine(busStationName + " is missing from the bus_stations table");
                Console.ReadLine();
                return;
            }

            int bsid = _bsnames[busStationName];

            if (_lastRouteID != routeid)
            {
                _lastRouteID = routeid;
                _seq = 0;
                Debug.Assert(duration == 0);
            }
            else
            {
                Debug.Assert(duration != 0);
            }

            string sql = "INSERT INTO route_hops(route_id, stop, bs_id , duration) VALUES(" + routeid + "," + _seq + ", " + bsid + ", " + duration + ");";
            _seq += 10;

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }

        // used to create the above c# statements
        public void Builder()
        {
            const string sql = "use laostraveller; select routename.name, b.name, route_hops.duration from route_hops, routename, bus_stations as b where b.bs_id = route_hops.bs_id and route_hops.route_id = routename.route_id  order by routename.route_id, stop;";

            var ds = new DataSet();

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var myReader = cmd.ExecuteReader())
                    {
                        ds.Load(myReader, LoadOption.OverwriteChanges, new[] { "table" });
                        myReader.Close();
                    }
                }

                conn.Close();
            }

            foreach (DataRow row in ds.Tables["table"].Rows)
            {
                Debug.WriteLine("Add1(\"" + row[0] + "\", \"" + row[1] + "\", " + row[2] + ");");
            }
        }
    }
}
